using System;
using System.Collections.Generic;
using System.Text;

using student_factory.Metier;

using System.Data;
using System.Data.SqlClient;

namespace student_factory.DAO
{
    class EtudiantDAO
    {

        private static String connection = "data source=localhost;initial catalog=STUDENTS_FACTORY;User ID=sa;Password=";

        public static IList<Etudiant> getNoProjetEtudiant(string idProfil)
        {       
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            IList<Etudiant> etudiants = new List<Etudiant>();

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                commande.CommandText = "SELECT ID_E, ETU_NOM, ETU_PRENOM FROM ETUDIANTS WHERE ID_P IS NULL AND ID_I = '" + idProfil + "'";

                reader = commande.ExecuteReader();

                while (reader.Read())
                {
                    Etudiant etu = new Etudiant();
                    etu.Id_e = reader["ID_E"].ToString();
                    etu.Etu_nom = reader["ETU_NOM"].ToString();
                    etu.Etu_prenom = reader["ETU_PRENOM"].ToString();
                    etudiants.Add(etu);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return etudiants;
        }

        public static bool setProject(string idProjet, string idEtudiant)
        {
            SqlConnection connexion = null;
            IList<Etudiant> projets = new List<Etudiant>();

            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = "data source=localhost;initial catalog=STUDENTS_FACTORY;User ID=sa;Password=;Asynchronous Processing=true;";

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();
                
                commande.CommandText = "UPDATE ETUDIANTS SET ID_P = '" + idProjet + "' WHERE ID_E = '" + idEtudiant + "'";

                commande.BeginExecuteNonQuery();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
                return false;
            }
            finally
            {

                if (connexion != null)
                    connexion.Close();
            }

            return true;
        }

        public static IList<Etudiant> getEtudiantsFromProject(Projet p)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            IList<Etudiant> profiles = new List<Etudiant>();
            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();

                commande.CommandText = "SELECT * from ETUDIANTS WHERE ID_P LIKE '" + p.Id_p + "'";

                reader = commande.ExecuteReader();



                while (reader.Read())
                {
                    string id_e = reader["ID_E"].ToString();
                    string id_i = reader["ID_I"].ToString();
                    string id_p = reader["ID_P"].ToString();
                    string etu_nom = reader["ETU_NOM"].ToString();
                    string etu_prenom = reader["ETU_PRENOM"].ToString();
                    string etu_date_naissance = reader["ETU_DATE_NAISSANCE"].ToString();
                    Etudiant etudiant = new Etudiant(id_e, id_i, id_p, etu_nom, etu_prenom, etu_date_naissance);

                    profiles.Add(etudiant);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return profiles;
        }

        public static Etudiant getEtudiantById(string id_etudiant)
        {
            SqlConnection connexion = null;
            SqlDataReader reader = null;
            Etudiant etu = null;
            try
            {
                connexion = new SqlConnection();
                connexion.ConnectionString = connection;

                connexion.Open();

                SqlCommand commande = connexion.CreateCommand();

                commande.CommandText = "SELECT * from ETUDIANTS WHERE ID_E LIKE '" + id_etudiant + "'";

                reader = commande.ExecuteReader();



                while (reader.Read())
                {
                    string id_e = reader["ID_E"].ToString();
                    string id_i = reader["ID_I"].ToString();
                    string id_p = reader["ID_P"].ToString();
                    string etu_nom = reader["ETU_NOM"].ToString();
                    string etu_prenom = reader["ETU_PRENOM"].ToString();
                    string etu_date_naissance = reader["ETU_DATE_NAISSANCE"].ToString();

                    etu = new Etudiant(id_e, id_i, id_p, etu_nom, etu_prenom, etu_date_naissance);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();

                if (connexion != null)
                    connexion.Close();
            }

            return etu;
        }
    }
}
